begin;
drop view certificate_of_origin_view;
create or replace view certificate_of_origin_view as
select commercial_id, jb.company_name as buyer, ja.company_name as account, pre_carriage, place_of_receipt, shipment_mode_name, city_name, mp1.port_name as discharge, mp2.port_name as destination, marks_and_nos, no_of_pkgs, description, quantity_type_id, "value", name_of_signatory,total_val,total_qty,invoice_no,
invoice_date from certificate_of_origin_commercial coo
left join _mcompany jb on jb.company_id=coo.buyer_id
left join _mcompany ja on ja.company_id=coo.account_id
left join _mship_mode ms on ms.shipment_mode_id=coo.ship_mode_id
left join _mport mp1 on mp1.port_id=coo.port_dis
left join _mport mp2 on mp2.port_id=coo.port_des
left join _mcity mc on mc.city_id=coo.ship_from_city;
commit;